Skip Headers

Oracle® Database Administrator's Guide 10g Release 2 (10.2) Part Number B14231-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 25 Moving from DBMS_JOB to DBMS_SCHEDULER

Oracle Database provides advanced scheduling capabilities through the Oracle Scheduler. The Scheduler offers far more functionality than the `DBMS_JOB` package, which was the previous Oracle Database job scheduler. This chapter discusses briefly how you can take statements created with `DBMS_JOB` and rewrite them using `DBMS_SCHEDULER`, which is the package that you use to configure and operate the Scheduler.

See Also: The documentation on supplied PL/SQL packages that came with your Oracle9i software for information on the `DBMS_JOB` package

This chapter contains the following topic:

- Moving from DBMS_JOB to DBMS_SCHEDULER

## Moving from DBMS_JOB to DBMS_SCHEDULER

This section illustrates some examples of how you can take jobs created with the `DBMS_JOB` package and rewrite them using the `DBMS_SCHEDULER` package.

### Creating a Job

An example of creating a job using `DBMS_JOB` is the following:

    VARIABLE jobno NUMBER;
    BEGIN
    DBMS_JOB.SUBMIT(:jobno, 'INSERT INTO employees VALUES (7935, ''SALLY'',
       ''DOGAN'', ''sally.dogan@xyzcorp.com'', NULL, SYSDATE, ''AD_PRES'', NULL,
        NULL, NULL, NULL);', SYSDATE, 'SYSDATE+1');
    COMMIT;
    END;
    /

An equivalent statement using `DBMS_SCHEDULER` is the following:

    BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
       job_name          =>  'job1',
       job_type          =>  'PLSQL_BLOCK',
       job_action        =>  'INSERT INTO employees VALUES (7935, ''SALLY'',
         ''DOGAN'', ''sally.dogan@xyzcorp.com'', NULL, SYSDATE,''AD_PRES'', NULL,
          NULL, NULL, NULL);');
       start_date        =>  SYSDATE,
       repeat_interval   =>  'FREQ = DAILY; INTERVAL = 1');
    END;
    /

### Altering a Job

An example of altering a job using `DBMS_JOB` is the following:

    BEGIN
    DBMS_JOB.WHAT(31, 'INSERT INTO employees VALUES (7935, ''TOM'', ''DOGAN'',
       ''tom.dogan@xyzcorp.com'', NULL, SYSDATE,''AD_PRES'', NULL,
       NULL, NULL, NULL);');
    COMMIT;
    END;
    /

This changes the action for `JOB1` to insert a different value. An equivalent statement using `DBMS_SCHEDULER` is the following:

    BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE(
       name          => 'JOB1',
       attribute     => 'job_action',
       value         => 'INSERT INTO employees VALUES (7935, ''TOM'', ''DOGAN'',
          ''tom.dogan@xyzcorp.com'', NULL, SYSDATE, ''AD_PRES'', NULL,
          NULL, NULL, NULL);');
    END;
    /

### Removing a Job from the Job Queue

The following example removes a job using `DBMS_JOB`, where 14144 is the number of the job being run:

    BEGIN
    DBMS_JOB.REMOVE(14144);
    COMMIT;
    END;
    /

Using `DBMS_SCHEDULER`, you would issue the following statement instead:

    BEGIN
       DBMS_SCHEDULER.DROP_JOB('myjob1');
    END;
    /

See Also: PL/SQL Packages and Types Reference for more information about the `DBMS_SCHEDULER` package Chapter 26, "Overview of Scheduler Concepts" Chapter 27, "Using the Scheduler" Chapter 28, "Administering the Scheduler"